Part 1: Building a data import workflow using Cloud datasources and CFML

Connecting & interacting with DynamoDB on AWS

We’re going to kind of go backwards here with this web series, but I wanted to make sure we build out our scaffolding before we get into the meat of the actual code. As a reminder, we’ll be building a system which pulls data from a Google Sheet using BaseQL to generate a GraphQL endpoint (read-only) which will feed a page that allows the user to edit those items prior to pushing them into our DynamoDB No-SQL cloud database on AWS.

Many others have written about no-sql databases and we’ve had folks even do whole talks on them, so I’ll let you deep-dive into the weeds on that if you’d like, but in basic terms, a no-sql database is a database with no set schema. This doesn’t mean you cannot use or set a schema, but in the default usage, essentially the database stores JSON data with some other information that can be used for sorting, indexing (and yes, a schema if you choose to do so).

For simplicity I’ll be using DynamoDB in a very basic manner, simply pushing and pulling out JSON. A reminder here, AWS provides DynamoDB on its free tier with a huge amount of monthly read/writes and transfer limits. Even once you hit those limits, the costs are very low. It is a great service, and ColdFusion 2021 includes native hooks to allow us to interact with it.

If you’re going to follow along, I recommend that you sign up for an AWS free-tier account here: https://aws.amazon.com/free/. That page also lists out the other free forever, free-trial and free-year offers available. Lots of fun stuff to play with.

Once you sign up for your free tier account, bounce over to https://aws.amazon.com/dynamodb (make sure you’re logged in) and create a new table. Remember what you named it.

Log into your CF Administrator. If you’ve installed the full version of CF2021, go ahead and move to the next step. Otherwise, click into the package manager and install “awsdynamodb” package.  It should be listed under “available packages”. If it is listed under “installed packages” you’re good to go.

Click into “Data & Services” and select the “Cloud Credentials” tab. We’re going to add a new AWS cloud credential here. Name the alias something you’ll remember. You’ll need your security credentials. You can figure out how to get those here. The “region” will be something like “us-east-1”. Save your information.

Now click to “Cloud Configuration”. Here is where we will be creating the DynamoDB service object that we’ll call to later. Name it something you’ll remember. The vendor is AWS, the Service Name is DynamoDB. I believe you can just leave all the options blank (you may need to fill in the Proxy Settings / Username and Password sections.

OK, now let’s do a quick test of the connection.

In a .cfm page put the following code:

<cfscript> 
dynamoObject = getCloudService("NAMEOFCLOUDCRED", "NAMEOFCLOUDCONFIG"); 
listTablesStruct = { 
"Limit": 50 
} 
listTablesResponse =dynamoObject.ListTables(listTablesStruct); 
writeDump(listTablesResponse);
</cfscript>

Where “NAMEOFCLOUDCRED” is the name you gave your cloud credential object, and “NAMEOFCLOUDCONFIG” is the name of the config you gave for you DynamoDB object you created.

This should return back a struct with some info in it, including the table you created earlier. (You can find more detailed info and options here).

Great! So now that means your DynamoDB object is accessible, and that the information you entered for your AWS credentials works. Let’s test one more thing.

Replace the code above with the following:

<cfscript> 
dynamoObject = getCloudService("NAMEOFCLOUDCRED", "NAMEOFCLOUDCONFIG"); 
tableName = "NAMEOFYOURDYNAMOTABLE";
scanStruct = {
"TableName": "#tableName#"
}
putItemStruct = {
"TableName":"#TableName#",
"Item":{ 
"key":"#createUUID()#",
"title":"some title or whatever",
"airdate":"August 24, 2013",
"ingredients":"zucchini",
"dish":"chicken parm"
},
"ReturnValues":"NONE"
}
try{ 
putItemResponse=dynamoObject.putItem(putItemStruct,{"hasType": false}) 
writeOutput("Item inserted successfully in the table.") 
writeDump(putItemResponse) 
} 
catch (any e){ 
writeDump(e) 
}
</cfscript>

So what are we doing here? This is an example of inserting a value into DynamoDB. Remember to replace “NAMEOFYOURDYNAMOTABLE” with the actual name of your dynamo table. You’ll also note that I’m inserting a “key” by using ColdFusion’s createUUID() function. This is an index I created when creating my table originally, but if you did not do something similar, it isn’t necessary. You should get back “Item inserted successfully in the table”.  To view the data, hope back over to your DynamoDB dashboard on AWS, click “Tables” in the left menu and then click on the name of your table on the right. On the detail page that shows, click “Explore table items” to view the data.

That’s it for this week! Next week we’ll be building out the editable table structures based on the read-only GraphQL returns we’re getting from our Google Sheet.